package com.css.fxfzypg.manage.building.repository.impl;

import com.css.fxfzypg.manage.building.repository.BuildingReqository;
import com.css.fxfzypg.manage.building.repository.entity.BuildingEntity;
import com.css.fxfzypg.manage.building.vo.*;
import com.css.fxfzypg.manage.sampling.repository.entity.SamplingEntity;
import com.css.fxfzypg.modules.resultupload.vo.ResultMainEntityVO;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class BuildingReqositoryImpl implements BuildingReqository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    @Transactional
    public boolean saveBuilding(BuildingEntity buildingEntity) {
        String sql = "insert into ypg_building_single values(:id,:taskCode,:taskName,:buildCode,:province,:provinceId,:city,:cityId,:county,:countyId,:country," +
                ":longitude,:latitude,:intensity,:buildYears,:purpose,:layerNum,:height,:terrainConditions,:foundationCondition,:planarRule,:verticalRule,:roofComponent," +
                ":housingCondition,:degreeDescription,:buildingStructure,:seismicCapacity,:remarks,:picture,:highStructureType,:steelStructureType," +
                ":hntConnectionType,:hntStairsPosition,:hntColumnSectionSize,:hntBeamSectionSize,:hntWeakLink,:hntMaxColumnCpacing,:hntMaximumFloorHeight,:hntMaxSpanSeam," +
                ":zhFloorType,:zhRoofType,:zhWallMaterials,:zhWallThicknessInterior,:zhWallThicknessExterior,:zhMortarType,:zhMortarStrength,:zhBearingSystem,:zhBearingMode," +
                ":zhStaircaseLocation,:zhHoleCrner,:zhStructuralColumn,:zhRingBeam," +
                ":zmRoofType,:zmWallMaterials,:zmMortar,:zmBearingSystem,:zmWallOpeningArea,:zmStructuralColumn,:zmRingBeam," +
                ":soilHardMountain,:soilWall,:soilModeType,:soilWallRingBeam,:soilWallDuctileMaterial,:soilDoorWindowWidth,:soilMaxSpacing,:soilRoofForm,:soilRoofingMaterials,:soilRoofTyingMeasures," +
                ":woodStructureType,:woodColumnsBracing,:woodColumnExtension,:woodNode,:woodTipDiameter,:woodFoundationAnchoring,:woodGableWall,:woodEnclosureWall,:woodWallConnection," +
                ":stoneWallMaterials,:stoneLoadMode,:stoneFloorStructure,:stoneRoofType,:stoneMaximumSpacing,:stoneWallOpeningArea,:stoneWallConnection,:stoneRingBeam,:stoneFourStructural," +
                ":isSubmit,:delFlag,:createName,:createId,:createTime,:submitter,:submitterId,:submitTime," +
                ":stoneWallMaterialsOther,:stoneRoofTypeOther,:zhStructuralColumnOther,:zhRingBeamOther,:zmRoofTypeOther,:zmStructuralColumnOther,:zmRingBeamOther," +
                ":roofComponentOther,:steelStructureTypeOther,:zhFloorTypeOther,:zhRoofTypeOther,:soilModeTypeOther,:specifyPicture,:fieldSurveyName,:fieldSurveyId, ST_GeometryFromText(:geom,4490))";
        SqlParameterSource source = new BeanPropertySqlParameterSource(buildingEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public Map<String, Object> querySubmitOrNot(String submitFlag, String taskCode, int curPage, int pageSize, String userId) {
        try {
            StringBuilder sql = new StringBuilder("select * from ypg_building_single where  del_flag = '0' and create_id = '" + userId + "' and task_code ='" + taskCode + "' and is_submit ='" + submitFlag + "'");
            if (submitFlag.equals("0")) {
                sql.append(" order by create_time desc");
            } else {
                sql.append(" order by submit_time desc");
            }
            int firstIndex = (curPage - 1) * pageSize;
            int lastIndex = pageSize;
            StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingEntity.class)).size();
            List<BuildingEntity> buildingEntityList = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(BuildingEntity.class));
            Map<String, Object> map = new HashMap();
            map.put("total", total);
            map.put("rows", buildingEntityList);
            return map;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public boolean updateIsSubmit(Map<String, String> map) {
        String sql = " update ypg_building_single set is_submit = 1, submitter = ?, submitter_id = ?, submit_time = ? where id = ?";
        return jdbcTemplate.update(sql, map.get("userName"), map.get("userId"), new Date(), map.get("id")) > 0;
    }

    @Override
    public BuildingEntity queryData(String id) {
        String sql = "select * from ypg_building_single where del_flag = '0' and id = ?";
        try {
            BuildingEntity buildingEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BuildingEntity.class), id);
            return buildingEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public boolean modifyData(BuildingEntity buildingEntity) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ypg_building_single set longitude=:longitude,latitude=:latitude, country=:country, intensity=:intensity,build_years=:buildYears,purpose=:purpose,layer_num=:layerNum,height=:height,terrain_conditions=:terrainConditions,foundation_condition=:foundationCondition," +
                "planar_rule=:planarRule,vertical_rule=:verticalRule,roof_component=:roofComponent,housing_condition=:housingCondition,degree_description=:degreeDescription," +
                "building_structure=:buildingStructure,seismic_capacity=:seismicCapacity,remarks=:remarks,picture=:picture," +
                "high_structure_type=:highStructureType,steel_structure_type=:steelStructureType,hnt_connection_type=:hntConnectionType,hnt_stairs_position=:hntStairsPosition," +
                "hnt_column_section_size=:hntColumnSectionSize,hnt_beam_section_size=:hntBeamSectionSize,hnt_weak_link=:hntWeakLink,hnt_max_column_cpacing=:hntMaxColumnCpacing," +
                "hnt_maximum_floor_height=:hntMaximumFloorHeight,hnt_max_span_seam=:hntMaxSpanSeam,zh_floor_type=:zhFloorType,zh_roof_type=:zhRoofType,zh_wall_materials=:zhWallMaterials," +
                "zh_wall_thickness_interior=:zhWallThicknessInterior,zh_wall_thickness_exterior=:zhWallThicknessExterior,zh_mortar_type=:zhMortarType,zh_mortar_strength=:zhMortarStrength," +
                "zh_bearing_system=:zhBearingSystem,zh_bearing_mode=:zhBearingMode,zh_staircase_location=:zhStaircaseLocation,zh_hole_crner=:zhHoleCrner,zh_structural_column=:zhStructuralColumn," +
                "zh_ring_beam=:zhRingBeam,zm_roof_type=:zmRoofType,zm_wall_materials=:zmWallMaterials,zm_mortar=:zmMortar,zm_bearing_system=:zmBearingSystem,zm_wall_opening_area=:zmWallOpeningArea," +
                "zm_structural_column=:zmStructuralColumn,zm_ring_beam=:zmRingBeam,soil_hard_mountain=:soilHardMountain,soil_wall=:soilWall,soil_mode_type=:soilModeType,soil_wall_ring_beam=:soilWallRingBeam," +
                "soil_wall_ductile_material=:soilWallDuctileMaterial,soil_door_window_width=:soilDoorWindowWidth,soil_max_spacing=:soilMaxSpacing,soil_roof_form=:soilRoofForm,soil_roofing_materials=:soilRoofingMaterials," +
                "soil_roof_tying_measures=:soilRoofTyingMeasures,wood_structure_type=:woodStructureType,wood_columns_bracing=:woodColumnsBracing,wood_column_extension=:woodColumnExtension,wood_node=:woodNode," +
                "wood_tip_diameter=:woodTipDiameter,wood_foundation_anchoring=:woodFoundationAnchoring,wood_gable_wall=:woodGableWall,wood_enclosure_wall=:woodEnclosureWall,wood_wall_connection=:woodWallConnection," +
                "stone_wall_materials=:stoneWallMaterials,stone_wall_materials_other=:stoneWallMaterialsOther,stone_load_mode=:stoneLoadMode,stone_floor_structure=:stoneFloorStructure,stone_roof_type=:stoneRoofType,stone_roof_type_other=:stoneRoofTypeOther,stone_maximum_spacing=:stoneMaximumSpacing," +
                "stone_wall_opening_area=:stoneWallOpeningArea,stone_wall_connection=:stoneWallConnection,stone_ring_beam=:stoneRingBeam,stone_four_structural=:stoneFourStructural,zh_structural_column_other=:zhStructuralColumnOther,zh_ring_beam_other=:zhRingBeamOther,zm_roof_type_other=:zmRoofTypeOther,zm_structural_column_other=:zmStructuralColumnOther,zm_ring_beam_other=:zmRingBeamOther," +
                "is_submit=:isSubmit,submitter=:submitter,submitter_id=:submitterId,submit_time=:submitTime,roof_component_other=:roofComponentOther,steel_structure_type_other=:steelStructureTypeOther,zh_floor_type_other=:zhFloorTypeOther,zh_roof_type_other=:zhRoofTypeOther,soil_mode_type_other=:soilModeTypeOther,field_survey_name=:fieldSurveyName,field_survey_id=:fieldSurveyId,specify_picture=:specifyPicture where id = '" + buildingEntity.getId() + "\'");
        SqlParameterSource source = new BeanPropertySqlParameterSource(buildingEntity);
        return namedParameterJdbcTemplate.update(sql.toString(), source) > 0;
    }

    @Override
    public Map<String, Object> queryBuildingData(HttpServletRequest request, BuildingParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.building_structure,t1.province,t1.city,t1.county,t1.country,t1.build_code,t1.create_id,t1.is_submit,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_building_single t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = " +
                "'1' and t2.task_status!='2'");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountyId())) {
            whereSql.append(" and t1.county_id = '" + queryParams.getCountyId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildingStructure())) {
            whereSql.append(" and t1.building_structure = '" + queryParams.getBuildingStructure() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildCode())) {
            whereSql.append(" and t1.build_code like '%" + queryParams.getBuildCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and t1.submit_time <= '" + queryParams.getEndTime() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountry())) {
            whereSql.append(" and t1.country like '%" + queryParams.getCountry() + "%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingParams.class)).size();
        sql.append(limitSql);
        List<BuildingParams> buildingData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", buildingData);
        return map;
    }

    @Override
    public boolean deleteBuilding(String id) {
        String sql = "update ypg_building_single set del_flag = 1  where id = ?";
        return jdbcTemplate.update(sql, id) > 0;
    }

    @Override
    public List<BuildingEntity> getDataGroupByStructure(String taskCode, String structure) {
        String sql = "select * from ypg_building_single where del_flag = '0' and task_code = '" + taskCode + "' and building_structure = '" + structure + "\'";
        List<BuildingEntity> buildingEntityList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BuildingEntity.class));
        return buildingEntityList;
    }

    @Override
    public List<BuildingEntity> getBuildingSingleData(List<String> idList) {
        String sql = "select * from ypg_building_single where del_flag = '0' and field_survey_id in (:idList) ";
        Map idMap = new HashMap();
        idMap.put("idList", idList);
        return namedParameterJdbcTemplate.query(sql, idMap, new BeanPropertyRowMapper<>(BuildingEntity.class));
    }

    @Override
    public List<BuildingParams> queryBuildingMap() {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.build_code,t1.longitude,t1.latitude,t1.building_structure,t1.create_id,t2.task_name,t2.task_status from ypg_building_single t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = '1' ");
        return jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(BuildingParams.class));
    }

    @Override
    public Map<String, Object> getBuildingDataByTeamMembers(HttpServletRequest request, BuildingParams queryParams, int curPage, int pageSize, String userId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT t1.id,t1.task_code,t1.longitude,t1.latitude,t1.building_structure,t1.province,t1.city,t1.county,t1.country,t1.build_code,t1.create_id,t1.is_submit,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_building_single t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit =" +
                " '1' and t2.task_status!='2'");
        StringBuilder inSql = new StringBuilder();
        //用于任务管理员的条件
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id= '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')  ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountyId())) {
            whereSql.append(" and t1.county_id = '" + queryParams.getCountyId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildingStructure())) {
            whereSql.append(" and t1.building_structure = '" + queryParams.getBuildingStructure() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildCode())) {
            whereSql.append(" and t1.build_code like '%" + queryParams.getBuildCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and t1.submit_time <= '" + queryParams.getEndTime() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountry())) {
            whereSql.append(" and t1.country like '%" + queryParams.getCountry() + "%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingParams.class)).size();
        List<BuildingParams> buildingData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(BuildingParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", buildingData);
        return map;
    }

    @Override
    public List<BuildingParams> getBuildingDataByTeamMembers(String userId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT t1.id,t1.task_code,t1.longitude,t1.latitude,t1.building_structure,t1.province,t1.city,t1.county,t1.country,t1.build_code,t1.create_id,t1.is_submit,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_building_single t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = '1' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        List<BuildingParams> buildingData = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(BuildingParams.class));
        return buildingData;
    }

    @Override
    public Map<String, Object> queryBuildingDataByProvince(BuildingParams queryParams, int curPage, int pageSize, String userId, String provinceId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT t1.id,t1.task_code,t1.longitude,t1.latitude,t1.building_structure,t1.province,t1.city,t1.county,t1.country,t1.build_code,t1.create_id,t1.is_submit,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_building_single t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit =" +
                " '1' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountyId())) {
            whereSql.append(" and t1.county_id = '" + queryParams.getCountyId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildingStructure())) {
            whereSql.append(" and t1.building_structure = '" + queryParams.getBuildingStructure() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildCode())) {
            whereSql.append(" and t1.build_code like '%" + queryParams.getBuildCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and t1.submit_time <= '" + queryParams.getEndTime() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountry())) {
            whereSql.append(" and t1.country like '%" + queryParams.getCountry() + "%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingParams.class)).size();
        List<BuildingParams> buildingData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(BuildingParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", buildingData);
        return map;
    }

    @Override
    public List<BuildingParams> queryBuildingDataByProvince(String userId, String provinceId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder("SELECT DISTINCT t1.id,t1.task_code,t1.longitude,t1.latitude,t1.building_structure,t1.province,t1.city,t1.county,t1.country,t1.build_code,t1.create_id,t1.is_submit,t1.submitter,t1.submit_time,t2.task_name,t2.task_status from ypg_building_single t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = '1' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        List<BuildingParams> buildingData = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(BuildingParams.class));
        return buildingData;
    }

    @Override
    public List<BuildingEntity> queryBuilding(BuildingParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT task_code,\n" +
                "build_code,\n" +
                "province,\n" +
                "province_id,\n" +
                "city,\n" +
                "city_id,\n" +
                "county,\n" +
                "county_id,\n" +
                "country,\n" +
                "longitude,\n" +
                "latitude,\n" +
                "intensity,\n" +
                "build_years,\n" +
                "purpose,\n" +
                "layer_num,\n" +
                "height,\n" +
                "terrain_conditions,\n" +
                "foundation_condition,\n" +
                "planar_rule,\n" +
                "vertical_rule,\n" +
                "roof_component,\n" +
                "housing_condition,\n" +
                "degree_description,\n" +
                "building_structure,\n" +
                "seismic_capacity,\n" +
                "remarks,\n" +
                "high_structure_type,\n" +
                "steel_structure_type,\n" +
                "hnt_connection_type,\n" +
                "hnt_stairs_position,\n" +
                "hnt_column_section_size,\n" +
                "hnt_beam_section_size,\n" +
                "hnt_weak_link,\n" +
                "hnt_max_column_cpacing,\n" +
                "hnt_maximum_floor_height,\n" +
                "hnt_max_span_seam,\n" +
                "zh_floor_type,\n" +
                "zh_roof_type,\n" +
                "zh_wall_materials,\n" +
                "zh_wall_thickness_interior,\n" +
                "zh_wall_thickness_exterior,\n" +
                "zh_mortar_type,\n" +
                "zh_mortar_strength,\n" +
                "zh_bearing_system,\n" +
                "zh_bearing_mode,\n" +
                "zh_staircase_location,\n" +
                "zh_hole_crner,\n" +
                "zh_structural_column,\n" +
                "zh_ring_beam,\n" +
                "zm_roof_type,\n" +
                "zm_wall_materials,\n" +
                "zm_mortar,\n" +
                "zm_bearing_system,\n" +
                "zm_wall_opening_area,\n" +
                "zm_structural_column,\n" +
                "zm_ring_beam,\n" +
                "soil_hard_mountain,\n" +
                "soil_wall,\n" +
                "soil_mode_type,\n" +
                "soil_wall_ring_beam,\n" +
                "soil_wall_ductile_material,\n" +
                "soil_door_window_width,\n" +
                "soil_max_spacing,\n" +
                "soil_roof_form,\n" +
                "soil_roofing_materials,\n" +
                "soil_roof_tying_measures,\n" +
                "wood_structure_type,\n" +
                "wood_columns_bracing,\n" +
                "wood_column_extension,\n" +
                "wood_node,\n" +
                "wood_tip_diameter,\n" +
                "wood_foundation_anchoring,\n" +
                "wood_gable_wall,\n" +
                "wood_enclosure_wall,\n" +
                "wood_wall_connection,\n" +
                "stone_wall_materials,\n" +
                "stone_load_mode,\n" +
                "stone_floor_structure,\n" +
                "stone_roof_type,\n" +
                "stone_maximum_spacing,\n" +
                "stone_wall_opening_area,\n" +
                "stone_wall_connection,\n" +
                "stone_ring_beam,\n" +
                "stone_four_structural,\n" +
                "is_submit,\n" +
                "del_flag,\n" +
                "stone_wall_materials_other,\n" +
                "stone_roof_type_other,\n" +
                "zh_structural_column_other,\n" +
                "zh_ring_beam_other,\n" +
                "zm_roof_type_other,\n" +
                "zm_structural_column_other,\n" +
                "zm_ring_beam_other,\n" +
                "roof_component_other,\n" +
                "steel_structure_type_other,\n" +
                "zh_floor_type_other,\n" +
                "zh_roof_type_other,\n" +
                "soil_mode_type_other from ypg_building_single ");
        StringBuilder whereSql = new StringBuilder("where del_flag = '0' and is_submit = '1' ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and province_id = '" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and city_id = '" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountyId())) {
            whereSql.append(" and county_id = '" + queryParams.getCountyId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildingStructure())) {
            whereSql.append(" and building_structure = '" + queryParams.getBuildingStructure() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildCode())) {
            whereSql.append(" and build_code like '%" + queryParams.getBuildCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + queryParams.getEndTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by submit_time desc");
        return jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(BuildingEntity.class));
    }

    @Override
    public List<BuildingEntity> queryBuildingByProvince(BuildingParams queryParams, String userId, String provinceId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder("SELECT t1.* from ypg_building_single t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t1.is_submit = '1' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountyId())) {
            whereSql.append(" and t1.county_id = '" + queryParams.getCountyId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildingStructure())) {
            whereSql.append(" and t1.building_structure = '" + queryParams.getBuildingStructure() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildCode())) {
            whereSql.append(" and t1.build_code like '%" + queryParams.getBuildCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and t1.submit_time <= '" + queryParams.getEndTime() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        List<BuildingEntity> buildingData = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(BuildingEntity.class));
        return buildingData;
    }

    @Override
    public List<BuildingEntity> queryBuildingDataByTeamMembers(BuildingParams queryParams, String userId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT t1.* from ypg_building_single t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = '1' ");
        StringBuilder inSql = new StringBuilder();
        //用于任务管理员的条件
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id= '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')  ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountyId())) {
            whereSql.append(" and t1.county_id = '" + queryParams.getCountyId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildingStructure())) {
            whereSql.append(" and t1.building_structure = '" + queryParams.getBuildingStructure() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildCode())) {
            whereSql.append(" and t1.build_code like '%" + queryParams.getBuildCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and t1.submit_time <= '" + queryParams.getEndTime() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        List<BuildingEntity> buildingData = jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(BuildingEntity.class));
        return buildingData;
    }

    @Override
    public List<BuildingResultVo> queryBuildingData(String taskCode, String province, String city, String county) {
        String sql = "SELECT province,city,county,building_structure,seismic_capacity,specify_picture  FROM ypg_building_single where province='" + province + "' and city='" + city + "' and county='" + county + "' and task_code='" + taskCode + "'  order by building_structure";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BuildingResultVo.class));
    }

    @Override
    public List<BuildingResultVo> queryBuildingData(String province, String city, String county) {
        String sql = "SELECT province,city,county,building_structure,seismic_capacity,specify_picture  FROM ypg_building_single where province='"+province+"' and city='"+city+"' and county='"+county+"'  order by building_structure";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BuildingResultVo.class));
    }

    @Override
    public List<BuildingResultVo> queryBuildingData(String samplingId) {
        /*String sql = "SELECT province,city,county,building_structure,seismic_capacity,specify_picture  " +
                "FROM ypg_building_single where task_code='"+taskCode+"' and field_survey_id='"+samplingId+"' " +
                "order by building_structure";*/

        String newsql = "SELECT province,city,county,building_structure,seismic_capacity,specify_picture  " +
                "FROM ypg_building_single where field_survey_id='" + samplingId + "' and del_flag='0'" +
                "order by building_structure";
        return jdbcTemplate.query(newsql, new BeanPropertyRowMapper<>(BuildingResultVo.class));
    }


    @Override
    public List<BuildingEntityVO> queryBuildingEntityData(String taskCode, String province, String city, String county) {
        String sql = "SELECT province,city,county,building_structure,seismic_capacity,field_survey_name,specify_picture  FROM ypg_building_single where province='" + province + "' and city='" + city + "'  and task_code='" + taskCode + "'";
        if (!"".equals(county)) {
            sql += " and county='" + county + "'";
        }
        sql += " order by building_structure";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BuildingEntityVO.class));
    }

    @Override
    public List<BuildingEntityVO> queryBuildingEntityData(String province, String city, String county, String survey, String type) {
        String sql = "SELECT province,city,county,building_structure,seismic_capacity,field_survey_name,specify_picture  FROM ypg_building_single where province='" + province + "' and city='" + city + "'  and building_structure='" + type + "'";
        if (!"".equals(county)) {
            sql += " and county='" + county + "'";
        }
        if (!"".equals(survey)) {
            sql += " and field_survey_name='" + survey + "'";
        }
        sql += " order by building_structure";
        List<BuildingEntityVO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BuildingEntityVO.class));
        return list.size() > 0 ? list : null;
    }

    @Override
    public List<BuildingEntity> getByFieldSurveyNameAndTaksCodeAndProvinceId(String dynName, String taskNum, String provinceId) {
        String sql = String.format("select * from ypg_building_single where task_code = '%s' and field_survey_name = '%s' and province_id = '%s'", taskNum, dynName, provinceId);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BuildingEntity.class));
    }

    @Override
    public Integer countBySurveyNameAndTaksCodeAndProvinceIdAndStructure(String name, String taskNum, String provinceId, String highStructure) {
        String sql = String.format("select count(*) from ypg_building_single where task_code = '%s' and field_survey_name = '%s' and province_id = '%s' and building_structure = '%s'", taskNum, name, provinceId, highStructure);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public SamplingEntity getSamplingEntityById(String fieldSurveyId) {
        String sql = "select * from ypg_sampling_survey_new where id ='"+fieldSurveyId+"'";
        List<SamplingEntity> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SamplingEntity.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query.get(0):null;
    }

    @Override
    public List<GcExcelVo> queryBuilding1(BuildingParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT * from ypg_building_single ");
        StringBuilder whereSql = new StringBuilder("where del_flag = '0' and is_submit = '1' ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and province_id = '" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and city_id = '" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountyId())) {
            whereSql.append(" and county_id = '" + queryParams.getCountyId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildingStructure())) {
            whereSql.append(" and building_structure = '" + queryParams.getBuildingStructure() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildCode())) {
            whereSql.append(" and build_code like '%" + queryParams.getBuildCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + queryParams.getEndTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by submit_time desc");
        return jdbcTemplate.query(sql.append(whereSql).append(orderSql).toString(), new BeanPropertyRowMapper<>(GcExcelVo.class));
    }
    private String buildSql(BuildingParams queryParams){
        StringBuilder sql = new StringBuilder("SELECT * from ypg_building_single ");
        StringBuilder whereSql = new StringBuilder("where del_flag = '0' and is_submit = '1' ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and province_id = '" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and city_id = '" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCountyId())) {
            whereSql.append(" and county_id = '" + queryParams.getCountyId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildingStructure())) {
            whereSql.append(" and building_structure = '" + queryParams.getBuildingStructure() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getBuildCode())) {
            whereSql.append(" and build_code like '%" + queryParams.getBuildCode() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            whereSql.append(" and submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + queryParams.getEndTime() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        StringBuilder orderSql = new StringBuilder(" order by submit_time desc");
        return sql.append(whereSql).append(orderSql).toString();
    }
    @Override
    public List<GExcelVo> queryBuilding2(BuildingParams queryParams) {
        return jdbcTemplate.query(buildSql(queryParams), new BeanPropertyRowMapper<>(GExcelVo.class));
    }

    @Override
    public List<DcgjhntExcelVo> queryBuilding3(BuildingParams queryParams) {
        return jdbcTemplate.query(buildSql(queryParams), new BeanPropertyRowMapper<>(DcgjhntExcelVo.class));
    }

    @Override
    public List<ZhExcelVo> queryBuilding4(BuildingParams queryParams) {
        return jdbcTemplate.query(buildSql(queryParams), new BeanPropertyRowMapper<>(ZhExcelVo.class));
    }

    @Override
    public List<ZmExcelVo> queryBuilding5(BuildingParams queryParams) {
        return jdbcTemplate.query(buildSql(queryParams), new BeanPropertyRowMapper<>(ZmExcelVo.class));
    }

    @Override
    public List<TExcelVo> queryBuilding6(BuildingParams queryParams) {
        return jdbcTemplate.query(buildSql(queryParams), new BeanPropertyRowMapper<>(TExcelVo.class));
    }

    @Override
    public List<MExcelVo> queryBuilding7(BuildingParams queryParams) {
        return jdbcTemplate.query(buildSql(queryParams), new BeanPropertyRowMapper<>(MExcelVo.class));
    }

    @Override
    public List<SExcelVo> queryBuilding8(BuildingParams queryParams) {
        return jdbcTemplate.query(buildSql(queryParams), new BeanPropertyRowMapper<>(SExcelVo.class));
    }

    @Override
    public List<QtExcelVo> queryBuilding9(BuildingParams queryParams) {
        return jdbcTemplate.query(buildSql(queryParams), new BeanPropertyRowMapper<>(QtExcelVo.class));
    }


}
